const express = require('express')
const router = express.Router()
const mysql = require('mysql')
const { status200, status500 } = require('../status')
const { filterQuery, pinjieSql, filterArrs, recursion, pinjiemohu, deleteMore, createIds, pinjieEdit } = require('../util/tool')
const multer = require('multer')
const path = require('path')
const { createId } = require('../util/index')
const { env } = require('../dataBase')
// const env = {
//     host: "111.231.22.97",
//     user: "cqbase",
//     password: "MZM5j38RNCjdPRP7",
//     database: "cqbase", //数据库名字
//     multipleStatements: true //允许多条sql语句查询
// }


//图片保存地址
const uploadURL = path.join(__dirname, '/file')
router.use(express.static(uploadURL))


//新增图片
const storage = multer.diskStorage({
    destination: (req, file, cb) => {
        cb(null, uploadURL);
    },
    filename: (req, file, cb) => {
        file.originalname = Buffer.from(file.originalname, "latin1").toString(
            "utf-8"
        );
        console.log(file.originalname)
        cb(null, file.originalname);
    },
});
const upload = multer({ storage: storage });
router.post('/addImg', upload.single('file'), async (req, res) => {
    if (req.file) {
        status200(res, { msg: '上传成功', data: { url: req.file.originalname } })
    }
    else {
        status500(res, '上传失败')
    }
})


//设置显示页数
const postNumber = (req, res) => {
    let sql = 'insert into carousel values(?,?,?,?)'
    let pool = mysql.createPool(env)
    let { count, date, author } = req.body
    pool.query(sql, [createId(), count, date, author], (err, resp) => {
        try {
            if (resp.affectedRows >= 1) {
                status200(res, { msg: '提交成功' })
            }
            else {
                status500(res, { msg: '提交失败' })
            }
        } catch {
            res.status(500)
            console.log(err)
        }
    })
}
router.post('/postNumber', (req, res) => {
    postNumber(req, res)
})


//国家查询联赛(精确)
router.get('/queryLeague', (req, res) => {
    let pool = mysql.createPool(env)
    pool.query('select * from leaguelist where country = ?', [req.query['country']], (err, resp) => {
        try {
            status200(res, { msg: 'success', data: { data: resp } })

        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
})
//联赛查询球队(精确)
router.get('/queryTeam', (req, res) => {
    let pool = mysql.createPool(env)
    pool.query('select * from teamlist where form = ?', [req.query['form']], (err, resp) => {
        try {
            status200(res, { msg: 'success', data: { data: resp } })

        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
})


//联赛放置一起
//设置查询联赛

const getLeague = async (req, res) => {
    let arr = await filterQuery(req.query)
    let pool = mysql.createPool(env)
    if (arr.length == 0) {
        let sql = 'select * from leaguelist'
        pool.query(sql, [], (err, result) => {
            try {
                status200(res, { data: { data: result, total: result.length } })
            } catch {
                res.status(500)
                console.log(err, 'error')
            }
        })
    }
    else {
        //模糊查询
        if (req.query['keyword']) {
            //默认国际，联赛名称，等级查询
            let sql = 'select * from leaguelist where '
            let arrs = ['league', 'country', 'level']
            let sqls = await pinjiemohu(sql, arrs, req)
            pool.query(sqls, (err, result) => {
                try {
                    status200(res, { data: { data: result, total: result.length } })
                } catch {
                    console.log(err, 'error')
                }
            })
        }
    }
}

router.get('/getLeague', (req, res) => {
    getLeague(req, res)
})


//新增
//查是否存在
const isData = (req) => {
    return new Promise((resolve, reject) => {
        let sql = 'select * from leaguelist where country = ? and level = ?'
        let pool = mysql.createPool(env)
        pool.query(sql, [req.body['country'], req.body['level']], (err, result) => {
            try {
                if (result.length > 0) {
                    resolve(false)
                }
                else {
                    resolve(true)
                }
            } catch {
                console.log(err, 'error')
            }
        })
    })
}
//判断有无联赛有无上一级,无上一级不给添加
const isLevel = (req) => {
    return new Promise((resolve, reject) => {
        let pool = mysql.createPool(env)
        let sql = 'select * from leaguelist where country = ? and level = ?'
        let num = Number(req.body['level']) - 1
        pool.query(sql, [req.body['country'], num], (err, result) => {
            try {
                if (result.length == 0) {
                    resolve(false)
                }
                else {
                    resolve(true)
                }
            } catch {
                console.log(err, 'error')
            }
        })
    })
}

//查询上级联赛id绑定
const queryUp = (req) => {
    return new Promise((resolve, reject) => {
        let level = Number(req.body['level']) - 1
        let sql = 'select * from leaguelist where country = ? and level = ?'
        let pool = mysql.createPool(env)
        pool.query(sql, [req.body['country'], level], (err, resp) => {
            try {
                if (resp[0]) {
                    resolve(resp[0].id)
                }
                else {
                    resolve(0)
                }
            } catch {
                console.log(err, 'error')
            }
        })
    })
}

//新增数据
const postLeague = async (req, res) => {
    let sql = 'insert into leaguelist values (?,?,?,?,?)'
    let { league, country, level } = req.body
    //找出最大值
    let pid = await queryUp(req)
    let queryArr = [createId(), pid, league, country, level]
    let pool = mysql.createPool(env)
    pool.query(sql, queryArr, (err, result) => {
        try {
            console.log(result)
            if (result.affectedRows >= 1) {
                status200(res, { msg: '提交成功' })
            }
            else {
                status500(res, { msg: '提交失败' })
            }
        } catch {
            console.log(err, 'error')
        }
    })
}

//判断本国第一级是否存在
const isCountryOne = (req) => {
    return new Promise((resolve, reject) => {
        console.log(req.body)
        let { country, level } = req.body
        let sql = 'select * from leaguelist where country = ? and level = ? '
        let pool = mysql.createPool(env)
        pool.query(sql, [country, level], (err, result) => {
            try {
                if (result[0]) {
                    resolve(false)
                }
                else {
                    resolve(true)
                }

            } catch {
                resolve(false)
            }
        })
    })
}


router.post('/postLeague', async (req, res) => {
    let isDatas = await isData(req)
    let isLevels = await isLevel(req)
    let isCountryOnes = await isCountryOne(req)
    if (req.body['level'] != 1) {
        if (!isDatas) {
            status500(res, { msg: '该联赛已注册' })
            return
        }
        if (!isLevels) {
            status500(res, { msg: '上级联赛未确定' })
            return
        }
    }
    else if (req.body['country'] != "" && req.body['level'] == 1) {
        if (!isCountryOnes) {
            status500(res, { msg: '该联赛已注册' })
            return
        }
    }
    postLeague(req, res)
})



//删除

//递归向下查找所有id
const findAllId = (arr, ids) => {
    let arrs = []
    arr.forEach(ele => {
        if (ele.id == ids) {
            arrs.push(ele.id)
        }
        if (ele.pid == ids) {
            arrs.push(ele.id)
            arrs = [...arrs, ...findAllId(arr, ele.id)]
        }
    });
    return arrs
}

//去重
const fArr = (arr) => {
    let arrs = []
    arr.forEach(ele => {
        if (!arrs.includes(ele)) {
            arrs.push(ele)
        }
    })
    return arrs
}


//删除本级和下边所有级别
const deleteLeague = (req, res) => {
    let pool = mysql.createPool(env)
    let { id } = req.body
    let sql = 'select * from leaguelist'
    pool.query(sql, [], async (err, result) => {
        try {
            let arres = fArr(findAllId(result, id))
            let sqles = 'delete from leaguelist where id in '
            let sqls = await deleteMore(arres, sqles)
            console.log(sqls, 'sqls')
            pool.query(sqls, arres, (err, resp) => {
                try {
                    if (resp.affectedRows >= 1) {
                        status200(res, { msg: '删除成功' })
                    }
                    else {
                        status500(res, { msg: '删除失败' })
                    }
                } catch {
                    res.status(500)
                }
            })
        } catch {
            res.status(500)
            console.log(err)
        }
    })


}
router.post('/deleteLeague', (req, res) => {
    deleteLeague(req, res)
})


//获取详情
router.get('/getLeagueDetail', async (req, res) => {
    let pool = mysql.createPool(env)
    let { id } = req.query
    console.log(id)
    pool.query('select * from leaguelist where id = ?', [id], (err, result) => {
        try {
            console.log(result, 'result')
            status200(res, { msg: 'success', data: { data: result[0] } })
        } catch {
            res.status(500)
            console.log(err)
        }
    })
})


//编辑
//查询是否
const editLeague = (req, res) => {
    let pool = mysql.createPool(env)
    let { id, league } = req.body
    let sql = 'update leaguelist set league = ? where id = ? '
    pool.query(sql, [league, id], (err, result) => {
        try {
            if (result.affectedRows >= 1) {
                status200(res, { msg: '编辑成功' })
            }
            else {
                status500(res, { msg: '编辑失败' })
            }
        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
}
router.post('/editLeague', async (req, res) => {
    editLeague(req, res)
})




//球队管理

//全部
const getAll = () => {
    return new Promise((resolve, reject) => {
        let pool = mysql.createPool(env)
        pool.query('select * from teamlist ', (err, result) => {
            try {
                resolve(result)
            } catch {
                console.log(err)
            }
        })
    })
}

const getTeamList = async (req, res) => {
    let arr = await filterQuery(req.query)
    //全部查询
    if (arr.length == 0) {
        let arrs = await getAll()
        let total = arrs.length
        status200(res, { msg: "success", data: { data: arrs, total: total } })
    }
    else {
        let { keyword } = req.query
        let sql = 'select * from teamlist where country = ? or teamName = ? '
        let pool = mysql.createPool(env)
        pool.query(sql, [keyword, keyword], (err, result) => {
            try {
                status200(res, {
                    msg: 'success', data: {
                        data: result,
                        total: result.length
                    }
                })
            } catch {
                res.status(500)
                console.log(err, 'error')
            }
        })

    }
}

router.get('/getTeamList', (req, res) => {
    getTeamList(req, res)
})





//根据国家查找对应级别
const getLeagueForm = (req, res) => {
    let queryArr = []
    if (req.query.country) {
        let { country } = req.query
        queryArr.push(country)
    }

    let pool = mysql.createPool(env)
    let sql = 'select * from leaguelist where country = ?'
    pool.query(sql, queryArr, (err, result) => {
        try {
            status200(res, { msg: 'success', data: { data: result } })
        } catch {
            res.status(500)
            console.log(err)
        }
    })
}
router.get('/getLeagueForm', (req, res) => {
    getLeagueForm(req, res)
})


//新增队伍
const postTeamlist = (req, res) => {
    let number = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
    let { teamName,
        teamStadium,
        country,
        formId,
        level
    } = req.body
    let queryArr = [createId(number), teamName, teamStadium, formId, country, level]
    let pool = mysql.createPool(env)
    pool.query('insert into teamlist values(?,?,?,?,?,?)', queryArr, (err, result) => {
        try {
            if (result.affectedRows >= 1) {
                status200(res, { msg: '新增成功' })
            }
            else {
                status500(res, { msg: '新增失败' })
            }
        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
}
router.post('/postTeamlist', (req, res) => {
    postTeamlist(req, res)
})



//获取详情
const queryLeague = async (id) => {
    return new Promise((resolve, reject) => {
        let sql = 'select * from leaguelist where id = ? '
        let pool = mysql.createPool(env)
        pool.query(sql, [id], (err, result) => {
            resolve(result[0])
        })
    })
}

const getTeamDetail = async (req, res) => {
    let pool = mysql.createPool(env)
    let sql = 'select * from teamlist where id = ? '
    let { id } = req.query
    pool.query(sql, [id], async (err, result) => {
        try {
            if (result.length > 0) {
                let obj = await queryLeague(result[0]['form'])
                console.log(obj, 'obj')
                status200(res, { msg: 'success', data: { data: result, obj: obj } })
            }
            else {
                status500(res, { msg: '查无此数据' })
            }
        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
}

router.get('/getTeamDetail', (req, res) => {
    getTeamDetail(req, res)
})


//修改
const updateTeam = async (req, res) => {
    let { teamName, teamStadium, formId, country, level, id } = req.body
    console.log(formId, 'formId')
    let pool = mysql.createPool(env)
    let sql = 'update teamlist '
    let sqls = await pinjieEdit(sql, ['teamName', 'teamStadium', 'form', 'country', 'level'])
    console.log(sqls, 'sqls')
    pool.query(sqls, [teamName, teamStadium, formId, country, level, id], (err, result) => {
        try {
            if (result.affectedRows >= 1) {
                status200(res, { msg: '修改成功' })
            }
            else {
                status500(res, { msg: '修改失败' })
            }

        } catch {
            res.status(500)
            console.log(result)
        }
    })
}

router.post('/updateTeam', (req, res) => {
    updateTeam(req, res)
})



//删除
const deleteTeam = (req, res) => {
    let { id } = req.body
    let pool = mysql.createPool(env)
    pool.query('delete from teamlist where id = ?', [id], (err, result) => {
        try {
            if (result.affectedRows >= 1) {
                status200(res, { msg: '删除成功' })
            }
            else {
                status500(res, { msg: '删除失败' })
            }
        }
        catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
}

router.post('/deleteTeam', (req, res) => {
    deleteTeam(req, res)
})



//获取教练员详情
const getCoach = (req, res) => {
    let { team, year } = req.query
    console.log(team, 'ppp')
    let sql = 'select * from coachlist where team = ? and year = ?'
    let pool = mysql.createPool(env)
    pool.query(sql, [team, year], (err, result) => {
        try {
            console.log(result, 'resoo')
            status200(res, { msg: 'success', data: { data: result } })
        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
}

router.get('/getCoach', (req, res) => {
    getCoach(req, res)
})

//获取运动员详情
const getPlayer = (req, res) => {
    let { team, year } = req.query
    let sql = 'select * from playerlist where team = ? and year = ? '
    let pool = mysql.createPool(env)
    pool.query(sql, [team, year], (err, result) => {
        try {
            let resk = result
            // status200(res, { msg: 'success', data: { data: result } })
            pool.query('select * from playerdetail where team = ? and year = ? ', [team, year], (err, resp) => {
                try {
                    let datas = []
                    resk.forEach(el => {
                        resp.forEach(ele => {
                            if (el.id == ele.id) {
                                let obj = Object.assign(el, ele)
                                datas.push(obj)
                            }
                        })
                    })
                    status200(res, { msg: 'success', data: { data: datas, total: datas.length } })
                } catch {
                    res.status(500)
                    console.log(err, 'error')
                }
            })
        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })

}
router.get('/getPlayer', (req, res) => {
    getPlayer(req, res)
})


//新增教练(主教练一名)
const isPost = (req) => {
    return new Promise((resolve, reject) => {
        let sql = 'select * from coachlist where team = ?'
        let pool = mysql.createPool(env)
        pool.query(sql, [req.body['team']], (err, result) => {
            try {
                if (result.length > 0) {
                    resolve(false)
                }
                else {
                    resolve(true)
                }
            } catch {
                resolve(false)
            }
        })
    })
}

//新增教练(函数)
const postCoach = (req, res) => {
    let pool = mysql.createPool(env)
    let sql = 'insert into coachlist values (?,?,?,?,?)'
    let { name, age, year, team } = req.body
    let queryArr = [createId(), name, age, year, team]
    pool.query(sql, queryArr, (err, result) => {
        try {
            if (result.affectedRows >= 1) {
                status200(res, { msg: '新增成功' })
            }
            else {
                status500(res, { msg: '新增失败' })
            }

        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
}

router.post('/postCoach', async (req, res) => {
    let isPosts = await isPost(req)
    if (!isPosts) {
        status500(res, { msg: '主教练已存在' })
        return
    }
    postCoach(req, res)
})


//新增球员

//一年只允许注册一次 如需就修改

const isAddPlay = (req) => {
    return new Promise((resolve, reject) => {
        let sql = "select * from playerlist where name = ? and year = ?"
        let pool = mysql.createPool(env)
        pool.query(sql, [req.body['name'], req.body['year']], (err, result) => {
            try {
                if (result[0]) {
                    resolve(false)
                }
                else {
                    resolve(true)
                }

            } catch {
                resolve(false)
            }
        })
    })

}

const postPlayer = (req, res) => {
    let { name, age, roles, year, price, number, team } = req.body
    let id = createId()
    let queryArr = [id, name, age, roles, year, price, number, team]
    let pool = mysql.createPool(env)
    pool.query('insert into playerlist values(?,?,?,?,?,?,?,?)', queryArr, (err, result) => {
        try {
            console.log(result)
            if (result.affectedRows >= 1) {
                //新增详情
                let sql = 'insert into playerdetail values (?,?,?,?,?,?)'
                let arr = [id, '0', '0', year, team, name]
                pool.query(sql, arr, (err, resp) => {
                    try {
                        if (resp.affectedRows >= 1) {
                            status200(res, { msg: '新增成功' })
                        }
                        else {
                            status500(res, { msg: '新增失败' })
                        }

                    } catch {
                        res.status(500)
                        console.log(err, 'error')
                    }
                })
            }
            else {
                status200(res, { msg: '新增失败' })
            }
        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
}

router.post('/postPlayer', async (req, res) => {
    let isAdds = await isAddPlay(req)
    if (!isAdds) {
        status500(res, { msg: '该球员今年已注册' })
        return
    }
    postPlayer(req, res)
})


//获取球员详情

const getPriceList=async(arr)=>{
      return new Promise((resolve,reject)=>{
        let prices=[]
        let years=[]
        arr.forEach(ele=>{
         years.push(ele.year)
         prices.push(ele.price)
        })
        resolve({prices:prices.reverse(),years:years.reverse()})
      })
}

const getPlayerDetail = (req, res) => {
    let pool=mysql.createPool(env)
    pool.query('select * from playerlist where id = ?',[req.query['id']],(err,result)=>{
        try{
            if(result[0]){
                pool.query('select * from teamlist where id = ?;select * from playerlist where name = ?',[result[0].team,result[0].name],async(err,resp)=>{
                    result[0]['teamName']=resp[0][0]['teamName']
                    result[0].setData=await getPriceList(resp[1])
                    status200(res,{msg:'success',data:{data:result[0]}})
                })
            }
            else{
                status500(res,{msg:'查无此球员'})
            }
        }catch{
            res.status(500)
            console.log(err,'err')
        }
    })

}
router.get('/getPlayerDetail', (req, res) => {
    getPlayerDetail(req, res)
})



module.exports = router